package com.winning.pmph.service;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.winning.pmph.entity.*;
import com.winning.pmph.mapper.WordsMapper;
import com.winning.pmph.utils.LocalDateTimeUtil;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.jetbrains.annotations.NotNull;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;
import java.util.stream.Stream;

@Component
@Transactional
public class WordsService extends ServiceImpl<WordsMapper, Words> {
    @Resource
    private WordsMapper wordsMapper;
    @Resource
    private TagService tagService;
    @Resource
    private UserService userService;
    @Resource
    private ReleasedKnowledgeService releasedKnowledgeService;


    public void saveWords(Words words) {
        wordsMapper.saveBean(words);
    }


    public List<Words> queryWords(Words words, String radioStatus, String updateTime) {
        String sql = "";
        if (StringUtils.equals(radioStatus, "officialTable")) {
            sql = "select * from words where status = " + Words.PASS_YES + " and deleted =" + Words.DELETED_NO;
        } else if (StringUtils.equals(radioStatus, "exportTable")) {
            sql = "select * from words where status = " + Words.PASS_NO + " and deleted = " + Words.DELETED_NO + " and source_type like '%" + Words.STATUS_EXPORT + "%'";
        } else if (StringUtils.equals(radioStatus, "LoadTable")) {
            sql = "select * from words where status = " + Words.PASS_NO + " and deleted = " + Words.DELETED_NO + " and (source_type = '" + Words.STATUS_KNOWLEDGE + "' or source_type = '" + Words.STATUS_TERM + "' or source_type = '" + Words.STATUS_TAG + "')";
        } else {
            sql = "select * from words where deleted = " + Words.DELETED_YES;
        }
        sql = getWordSql(words, sql, updateTime);
        sql += " order by updated_time desc,id limit " + words.getCurrentPage() * words.getPageSize() + " , " + words.getPageSize();
        List<Words> wordsList = wordsMapper.executeMultiSelect(sql);
        if (StringUtils.equals(radioStatus, "LoadTable")) {
            for (Words words1 : wordsList) {
                parseTagName(words1);
            }
        }
        if (StringUtils.equals(radioStatus, "exportTable")) {
            for (Words words1 : wordsList) {
                User user = userService.getUserByUserName(words1.getCreatedBy());
                words1.setCreatedBy(user.getName());
            }
        }
        return wordsList;
    }

    private void parseTagName(Words words1) {
        if (StringUtils.isBlank(words1.getTag()) || StringUtils.equals(words1.getTag(), "[]")) {
            words1.setTag("");
            return;
        }
        List<String> list = new ArrayList<>();
        String[] split = words1.getTag().split(";");
        for (int i = 0; i < split.length; i++) {
            String name = split[i];
            // 二维数组  取内层数组最后一个元素
            List<String> tagIdList = JSON.parseArray(name).stream()
                    .map(innerArray -> JSON.parseArray(JSON.toJSONString(innerArray)))
                    .map(innerArray -> innerArray.getString(innerArray.size() - 1))
                    .collect(Collectors.toList());
            String tagSql = "select * from tag where  id in "
                    + tagIdList.stream().collect(Collectors.joining("','", "('", "')")) + " ORDER BY LEVEL";
            List<Tag> tags = tagService.getBaseMapper().executeMultiSelect(tagSql);
            Map<String, String> tagId2NameMap = tags.stream().collect(Collectors.toMap(Tag::getId, Tag::getName));
            list.add(tagIdList.stream().map(tagId2NameMap::get).collect(Collectors.joining(";")));
        }
        String collect = list.stream().collect(Collectors.joining(";"));
        words1.setTag(collect);
    }


    public int queryWordsNum(Words words, String radioStatus, String updateTime) {
        String sql = "";
        if (StringUtils.equals(radioStatus, "officialTable")) {
            sql = "select count(*) from words where status = " + Words.PASS_YES + " and deleted =" + Words.DELETED_NO;
        } else if (StringUtils.equals(radioStatus, "exportTable")) {
            sql = "select count(*) from words where status = " + Words.PASS_NO + " and deleted = " + Words.DELETED_NO + " and source_type like '%" + Words.STATUS_EXPORT + "%'";
        } else if (StringUtils.equals(radioStatus, "LoadTable")) {
            sql = "select count(*) from words where status = " + Words.PASS_NO + " and deleted = " + Words.DELETED_NO + " and (source_type = '" + Words.STATUS_KNOWLEDGE + "' or source_type = '" + Words.STATUS_TERM + "' or source_type = '" + Words.STATUS_TAG + "')";
        } else {
            sql = "select count(*) from words where deleted = " + Words.DELETED_YES;
        }
        sql = getWordSql(words, sql, updateTime);
        return wordsMapper.selectInt(sql);
    }


    public List<Words> getWordsAll(Words words, String radioStatus, String updateTime) {
        String sql = "";
        if (StringUtils.equals(radioStatus, "exportTable")) {
            sql = "select * from words where status = " + Words.PASS_NO + " and deleted = " + Words.DELETED_NO + " and source_type like '%" + Words.STATUS_EXPORT + "%'";
        } else {
            sql = "select * from words where status = " + Words.PASS_NO + " and deleted = " + Words.DELETED_NO + " and (source_type = '" + Words.STATUS_KNOWLEDGE + "' or source_type = '" + Words.STATUS_TERM + "' or source_type = '" + Words.STATUS_TAG + "')";
        }
        sql = getWordSql(words, sql, updateTime);
        return wordsMapper.executeMultiSelect(sql);
    }

    public void allAudit(List<Words> wordsList) {
        String ids = wordsList.stream().map(Words::getId).collect(Collectors.joining("','", "('", "')"));
        String updateSql = "update words set status = " + Words.PASS_YES
                + " ,updated_time = '" + LocalDateTimeUtil.getNowAsString() + "' " +
                " where id in " + ids;
        wordsMapper.executeUpdate(updateSql);
    }


    public int deleteWordById(String id) {
        return wordsMapper.deleteById(id);
    }


    public int deleteOfficialWordById(String id) {
        String sql = "update words set deleted = " + Words.DELETED_YES
                + ", updated_time = '" + LocalDateTimeUtil.getNowAsString()
                + "' where id='" + id + "'";
        return wordsMapper.executeUpdate(sql);
    }


    public int restoreWord(String id) {
        String sql = "update words set deleted = " + Words.DELETED_NO
                + ", updated_time = '" + LocalDateTimeUtil.getNowAsString()
                + "' where id='" + id + "'";
        return wordsMapper.executeUpdate(sql);
    }

    /**
     * 全部彻底删除
     */

    public void deleteAllWord() {
        wordsMapper.delete(new UpdateWrapper<Words>().eq("deleted", Words.DELETED_YES));
    }


    public void restoreAllWord() {
        String sql = "update words set deleted = " + Words.DELETED_NO
                + " ,updated_time = '" + LocalDateTimeUtil.getNowAsString()
                + "' where deleted=" + Words.DELETED_YES;
        wordsMapper.executeUpdate(sql);
    }

    private String getWordSql(Words words, String sql, String updateTime) {
        if (StringUtils.isNotBlank(words.getName())) {
            sql += " and ( name like '%" + words.getName() + "%' " +
                    " or english_name like '%" + words.getName() + "%' " +
                    " or synonym like '%" + words.getName() + "%' )";
        }
        if (StringUtils.isNotBlank(words.getTag())) {
            sql += " and tag like '%" + words.getTag() + "%'";
        }
        if (StringUtils.isNotBlank(words.getSourceType())) {
            sql += " and source_type like '%" + words.getSourceType() + "%'";
        }
        if (StringUtils.isNotBlank(words.getCreatedBy())) {
            User user = userService.getUserName(words.getCreatedBy());
            if (user != null) {
                sql += " and created_by =  '" + user.getUserName() + "'";
            } else {
                sql += " and created_by =  ''";
            }
        }
        if (StringUtils.isNotBlank(updateTime)) {
            String startTime = (updateTime.split(",")[0].trim());
            String endTime = (updateTime.split(",")[1].trim());
            sql += " and updated_time >= '" + startTime + "' " +
                    " and updated_time <= '" + endTime + "'";
        }
        return sql;
    }


    public void knowledgeAndTerm() {
        //检查库id
        String CHECK = "0001AA10000000092SNV";
        //疾病库阿里版本id
        String DISEASE_ALI = "0001AA1000000009BA7T";
        //检验项
        String INSPECTION_ITEM = "0001AA100000000AYAJI";
        //化验单
        String LABORATORY = "0001AA100000000AYALN";
        //穴位
        String POINT = "0001AA100000000DX6FT";
        //中成药
        String MEDICINE = "0001AA100000000DYLD5";
        //经络
        String CHANNELS = "0001AA100000000E0DEP";
        //中医疾病
        String TCM_DISEASE = "0001AA100000000EBXAX";
        //症状体征
        String SIGNS_SYMPTOMS = "0001AA100000000EII6P";
        //手术操作
        String OPERATION = "0001AA100000000EJXVT";
        //护理操作
        String NURSING_PROCEDURES = "0001AA100000000EKCJL";
        //疾病
        String DISEASE = "0001AA100000000EKLSX";
        //方剂
        String PRESCRIPTIONS = "0001AA100000000HNIUX";
        //中药
        String CHINESE_MEDICINE = "0001AA100000000HNP15";
        //临床操作
        String CLINICAL_OPERATION = "0001AA100000000HNV7D";
        //常见病处方
        String COMMON_PRESCRIPTION = "0001AA100000000HPH2P";
        //基本药物目录
        String ESSENTIAL_MEDICINES = "0001AA100000000SMJKA";
        //医保药品目录
        String MEDICARE_DRUG_DIRECTORY = "0001AA100000000HSE0H";
        //超说明书用药
        String OFF_LABEL_USE = "0001AA100000000J83W9";
        //脉象
        String PULSE_CONDITION = "0001AA100000000OKBJX";
        //中医药膳
        String MEDICINAL_FOOD = "0001AA100000000OKG1B";
        //药物信息
        String DRUG_INFORMATION = "0001AA100000000HUKPT";
        //药物相互作用
        String DRUG_INTERACTIONS = "0001AA100000000GECBD";
        //术语库到词库
        termStandard2words();
        //药物信息
        buildDrugInformation(DRUG_INFORMATION);
        //检查库
        buildCheck(CHECK);
        //疾病库阿里版本
        buildDiseaseAli(DISEASE_ALI);
        //检验项
        inspectionItemBuild(INSPECTION_ITEM);
        //化验单
        buildLaboratory(LABORATORY);
        //穴位
        buildPoint(POINT);
        //中成药
        buildMedicine(MEDICINE);
        //经络
        buildChannels(CHANNELS);
        //中医疾病
        buildTcmDisease(TCM_DISEASE);
        //症状体征 SIGNS_SYMPTOMS
        buildSignsSymptoms(SIGNS_SYMPTOMS);
        //手术操作 OPERATION
        buildOperation(OPERATION);
        //护理操作 NURSING_PROCEDURES
        buildNursingProcedures(NURSING_PROCEDURES);
        //疾病 DISEASE
        buildDisease(DISEASE);
        //方剂 PRESCRIPTIONS
        buildPrescriptions(PRESCRIPTIONS);
        //中药 CHINESE_MEDICINE
        buildChineseMedicine(CHINESE_MEDICINE);
        //临床操作 CLINICAL_OPERATION
        buildClinicalOperation(CLINICAL_OPERATION);
        //常见病处方 COMMON_PRESCRIPTION
        buildCommonPrescription(COMMON_PRESCRIPTION);
        //基本药物目录 ESSENTIAL_MEDICINES
        buildEssentialMedicines(ESSENTIAL_MEDICINES);
        //医保药物目录 MEDICARE_DRUG_DIRECTORY
        buildMedicareDrugDirectory(MEDICARE_DRUG_DIRECTORY);
        //超说明书用药 OFF_LABEL_USE
        buildOffLabelUse(OFF_LABEL_USE);
        //脉象 PULSE_CONDITION
        buildPulseCondition(PULSE_CONDITION);
        //中医药膳 MEDICINAL_FOOD
        buildMedicinalFood(MEDICINAL_FOOD);
        //药物相互作用 DRUG_INTERACTIONS
        buildDrugInteractions(DRUG_INTERACTIONS);
        //分类标签到词库
        tag2Words();
    }

    private void tag2Words() {
        String tagSql = "select * from tag where root_id in('0001AA1000000007YFQO','0001AA1000000007YJL5','0001AA1000000007YMO9',\n" +
                "'0001AA1000000007YQJ5','0001AA1000000007YS2P','0001AA100000000812MY','0001AA1000000008146H','0001AA10000000081884',\n" +
                "'0001AA1000000008UYAI','0001AA1000000009MK2X','0001AA100000000AYAKV','0001AA100000000EJ3T3','0001AA100000000KM0AK',\n" +
                "'2006024','2006025','4','6','7','8','0001AA100000000OYPXB','0001AA100000000OYPYL','0001AA100000000RR3BT') and updated_time > '" + LocalDate.now().plusDays(-1) + "'";
        List<Tag> tags = tagService.getBaseMapper().executeMultiSelect(tagSql);
        for (Tag tag : tags) {
            Words words = new Words();
            words.setKnowledgeId(tag.getId());
            words.setName(tag.getName());
            words.setSourceType(Words.STATUS_TAG);
            words.setStatus(Words.PASS_NO);
            words.setDeleted(Words.DELETED_NO);
            words.setUpdatedBy("自动导入");
            words.setCreatedBy("自动导入");
            words.setCreatedTime(LocalDateTimeUtil.getNowAsString());
            words.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
            Words dbWords = wordsMapper.selectBeanByWherePart(" knowledge_id='" + tag.getId() + "'");
            if (Objects.nonNull(dbWords)) {
                words.setId(dbWords.getId());
            }
            wordsMapper.saveBean(words);
        }

        String sql = "WITH RECURSIVE _children AS (SELECT t.* FROM tag t WHERE t.PARENT_ID = '7e1c86154377464f89d98c65b485d53f' UNION ALL\n" +
                "SELECT t.* FROM tag t INNER JOIN _children ON t.parent_id = _children.id ) \n" +
                " SELECT *FROM _children where level=7 and updated_time > '" + LocalDate.now().plusDays(-1) + "'  ORDER BY LEVEL,`index`";
        List<Tag> instrumentsTags = tagService.getBaseMapper().executeMultiSelect(sql);
        for (Tag tag : instrumentsTags) {
            Words words = new Words();
            words.setKnowledgeId(tag.getId());
            words.setName(tag.getName());
            words.setSourceType(Words.STATUS_TAG);
            words.setStatus(Words.PASS_NO);
            words.setDeleted(Words.DELETED_NO);
            words.setUpdatedBy("自动导入");
            words.setCreatedBy("自动导入");
            words.setCreatedTime(LocalDateTimeUtil.getNowAsString());
            words.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
            Words dbWords = wordsMapper.selectBeanByWherePart(" knowledge_id='" + tag.getId() + "'");
            if (Objects.nonNull(dbWords)) {
                words.setId(dbWords.getId());
            }
            wordsMapper.saveBean(words);
        }
    }

    private void buildDrugInteractions(String DRUG_INTERACTIONS) {
        List<Words> list = wordsMapper.lambdaQuery().eq(Words::getKnowledgeLibId, DRUG_INTERACTIONS).list();
        if (CollectionUtils.isNotEmpty(list)) {
            List<String> wordIds = list.stream().map(Words::getId).collect(Collectors.toList());
            wordsMapper.deleteBatchIds(wordIds);
        }
        String drugInteractionsSql = "select knowledge_id,\n" +
                "max(case name when '药物A' then property end) as name,\n" +
                "max(case name when '药物B' then property end) as name_bak,\n" +
                "max(case name when '药物C' then property end) as name_bak1,\n" +
                "max(case name when '药物D' then property end) as name_bak2,\n" +
                "max(case name when '药物E' then property end) as name_bak3,\n" +
                "max(case name when '药物A英文名称' then property end) as english_name,\n" +
                "max(case name when '药物B英文名称' then property end) as english_name_bak,\n" +
                "max(case name when '药物C英文名称' then property end) as english_name_bak1,\n" +
                "max(case name when '药物D英文名称' then property end) as english_name_bak2,\n" +
                "max(case name when '药物E英文名称' then property end) as english_name_bak3\n" +
                "from released_knowledge_field where\n" +
                "knowledge_id in (select id from released_knowledge where deleted=0 and knowledge_lib_id='" + DRUG_INTERACTIONS + "' )\n" +
                " group by knowledge_id";
        List<Words> wordsList = wordsMapper.executeMultiSelect(drugInteractionsSql);
        for (Words words : wordsList) {
            if (StringUtils.isNotBlank(words.getName())) {
                words.setName(words.getName());
                words.setEnglishName(words.getEnglishName());
                words.setKnowledgeId(words.getKnowledgeId());
                words.setKnowledgeLibId(DRUG_INTERACTIONS);
                words.setSourceType(Words.STATUS_KNOWLEDGE);
                words.setDeleted(Words.DELETED_NO);
                words.setStatus(Words.PASS_NO);
                words.setCreatedBy("自动导入");
                words.setUpdatedBy("自动导入");
                words.setCreatedTime(LocalDateTimeUtil.getNowAsString());
                words.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
                wordsMapper.saveBean(words);
            }
            if (StringUtils.isNotBlank(words.getNameBak())) {
                Words words1 = new Words();
                words1.setName(words.getNameBak());
                words1.setEnglishName(words.getEnglishNameBak());
                words1.setKnowledgeId(words.getKnowledgeId());
                words1.setKnowledgeLibId(DRUG_INTERACTIONS);
                words1.setSourceType(Words.STATUS_KNOWLEDGE);
                words1.setDeleted(Words.DELETED_NO);
                words1.setStatus(Words.PASS_NO);
                words1.setCreatedBy("自动导入");
                words1.setUpdatedBy("自动导入");
                words1.setCreatedTime(LocalDateTimeUtil.getNowAsString());
                words1.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
                wordsMapper.saveBean(words1);
            }
            if (StringUtils.isNotBlank(words.getNameBak1())) {
                Words words2 = new Words();
                words2.setName(words.getNameBak1());
                words2.setEnglishName(words.getEnglishNameBak1());
                words2.setKnowledgeId(words.getKnowledgeId());
                words2.setKnowledgeLibId(DRUG_INTERACTIONS);
                words2.setSourceType(Words.STATUS_KNOWLEDGE);
                words2.setDeleted(Words.DELETED_NO);
                words2.setStatus(Words.PASS_NO);
                words2.setCreatedBy("自动导入");
                words2.setUpdatedBy("自动导入");
                words2.setCreatedTime(LocalDateTimeUtil.getNowAsString());
                words2.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
                wordsMapper.saveBean(words2);
            }

            if (StringUtils.isNotBlank(words.getNameBak2())) {
                Words words3 = new Words();
                words3.setName(words.getNameBak2());
                words3.setEnglishName(words.getEnglishNameBak2());
                words3.setKnowledgeId(words.getKnowledgeId());
                words3.setKnowledgeLibId(DRUG_INTERACTIONS);
                words3.setSourceType(Words.STATUS_KNOWLEDGE);
                words3.setDeleted(Words.DELETED_NO);
                words3.setStatus(Words.PASS_NO);
                words3.setCreatedBy("自动导入");
                words3.setUpdatedBy("自动导入");
                words3.setCreatedTime(LocalDateTimeUtil.getNowAsString());
                words3.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
                wordsMapper.saveBean(words3);
            }
            if (StringUtils.isNotBlank(words.getNameBak3())) {
                Words words4 = new Words();
                words4.setName(words.getNameBak3());
                words4.setEnglishName(words.getEnglishNameBak3());
                words4.setKnowledgeId(words.getKnowledgeId());
                words4.setKnowledgeLibId(DRUG_INTERACTIONS);
                words4.setSourceType(Words.STATUS_KNOWLEDGE);
                words4.setDeleted(Words.DELETED_NO);
                words4.setStatus(Words.PASS_NO);
                words4.setCreatedBy("自动导入");
                words4.setUpdatedBy("自动导入");
                words4.setCreatedTime(LocalDateTimeUtil.getNowAsString());
                words4.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
                wordsMapper.saveBean(words4);
            }
        }
    }

    private void buildMedicinalFood(String MEDICINAL_FOOD) {
        String medicinalFoodSql = "select knowledge_id,\n" +
                "max(case name when '名称' then property end) as name,\n" +
                "max(case name when '药膳分类' then property end) as tag\n" +
                "from released_knowledge_field where\n" +
                "knowledge_id in (select id from released_knowledge where deleted=0 and knowledge_lib_id='" + MEDICINAL_FOOD + "' and updated_time > '" + LocalDate.now().plusDays(-1) + "' )\n" +
                " group by knowledge_id";
        List<Words> wordsArray = wordsMapper.executeMultiSelect(medicinalFoodSql);
        for (Words words : wordsArray) {
            words.setName(words.getName());
            words.setTag(words.getTag());
            words.setKnowledgeId(words.getKnowledgeId());
            words.setKnowledgeLibId(MEDICINAL_FOOD);
            words.setSourceType(Words.STATUS_KNOWLEDGE);
            words.setDeleted(Words.DELETED_NO);
            words.setStatus(Words.PASS_NO);
            words.setCreatedBy("自动导入");
            words.setUpdatedBy("自动导入");
            words.setCreatedTime(LocalDateTimeUtil.getNowAsString());
            words.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
            List<Words> dbWordsArray = wordsMapper.selectBeansByWherePart("knowledge_lib_id = '" + MEDICINAL_FOOD + "' and knowledge_id = '" + words.getKnowledgeId() + "'");
            if (CollectionUtils.isNotEmpty(dbWordsArray)) {
                words.setId(dbWordsArray.get(0).getId());
                words.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
            }
            wordsMapper.saveBean(words);
        }
    }

    private void buildPulseCondition(String PULSE_CONDITION) {
        String pulseConditionSql = "  select knowledge_id,\n" +
                "max(case name when '名称' then property end) as name,\n" +
                "max(case name when '脉象分类' then property end) as tag\n" +
                "from released_knowledge_field where\n" +
                "knowledge_id in (select id from released_knowledge where deleted=0 and knowledge_lib_id='" + PULSE_CONDITION + "' and updated_time > '" + LocalDate.now().plusDays(-1) + "')\n" +
                " group by knowledge_id";
        List<Words> wordsArray = wordsMapper.executeMultiSelect(pulseConditionSql);
        for (Words words : wordsArray) {
            words.setKnowledgeLibId(PULSE_CONDITION);
            words.setSourceType(Words.STATUS_KNOWLEDGE);
            words.setDeleted(Words.DELETED_NO);
            words.setStatus(Words.PASS_NO);
            words.setCreatedBy("自动导入");
            words.setUpdatedBy("自动导入");
            words.setCreatedTime(LocalDateTimeUtil.getNowAsString());
            words.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
            List<Words> dbWordsArray = wordsMapper.selectBeansByWherePart("knowledge_lib_id = '" + PULSE_CONDITION + "' and knowledge_id = '" + words.getKnowledgeId() + "'");
            if (CollectionUtils.isNotEmpty(dbWordsArray)) {
                words.setId(dbWordsArray.get(0).getId());
                words.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
            }
            wordsMapper.saveBean(words);
        }
    }

    private void buildOffLabelUse(String OFF_LABEL_USE) {
        String offLabelUseSql = "  select knowledge_id,\n" +
                "max(case name when '药品名称' then property end) as name,\n" +
                "max(case name when '英文名称' then property end) as english_name,\n" +
                "max(case name when '超说明书用药分类' then property end) as tag\n" +
                "from released_knowledge_field where\n" +
                "knowledge_id in (select id from released_knowledge where deleted=0 and knowledge_lib_id='" + OFF_LABEL_USE + "' and updated_time > '" + LocalDate.now().plusDays(-1) + "')\n" +
                " group by knowledge_id";
        List<Words> wordsArray = wordsMapper.executeMultiSelect(offLabelUseSql);
        for (Words words : wordsArray) {
            words.setKnowledgeLibId(OFF_LABEL_USE);
            words.setSourceType(Words.STATUS_KNOWLEDGE);
            words.setDeleted(Words.DELETED_NO);
            words.setStatus(Words.PASS_NO);
            words.setCreatedBy("自动导入");
            words.setUpdatedBy("自动导入");
            words.setCreatedTime(LocalDateTimeUtil.getNowAsString());
            words.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
            List<Words> dbWordsArray = wordsMapper.selectBeansByWherePart("knowledge_lib_id = '" + OFF_LABEL_USE + "' and knowledge_id = '" + words.getKnowledgeId() + "'");
            if (CollectionUtils.isNotEmpty(dbWordsArray)) {
                words.setId(dbWordsArray.get(0).getId());
            }
            wordsMapper.saveBean(words);
        }
    }

    private void buildMedicareDrugDirectory(String MEDICARE_DRUG_DIRECTORY) {
        String medicareDrugDirectorySql = " select knowledge_id,\n" +
                "max(case name when '药物名称' then property end) as name,\n" +
                "max(case name when '医保药物分类' then property end) as tag\n" +
                "from released_knowledge_field where\n" +
                "knowledge_id in (select id from released_knowledge where deleted=0 and knowledge_lib_id='" + MEDICARE_DRUG_DIRECTORY + "' and updated_time > '" + LocalDate.now().plusDays(-1) + "')\n" +
                " group by knowledge_id";
        List<Words> wordsArray = wordsMapper.executeMultiSelect(medicareDrugDirectorySql);
        for (Words words : wordsArray) {
            words.setKnowledgeLibId(MEDICARE_DRUG_DIRECTORY);
            words.setSourceType(Words.STATUS_KNOWLEDGE);
            words.setDeleted(Words.DELETED_NO);
            words.setStatus(Words.PASS_NO);
            words.setCreatedBy("自动导入");
            words.setUpdatedBy("自动导入");
            words.setCreatedTime(LocalDateTimeUtil.getNowAsString());
            words.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
            List<Words> dbWordsArray = wordsMapper.selectBeansByWherePart("knowledge_lib_id = '" + MEDICARE_DRUG_DIRECTORY + "' and knowledge_id = '" + words.getKnowledgeId() + "'");
            if (CollectionUtils.isNotEmpty(dbWordsArray)) {
                words.setId(dbWordsArray.get(0).getId());
            }
            wordsMapper.saveBean(words);
        }
    }

    private void buildEssentialMedicines(String ESSENTIAL_MEDICINES) {
        String essentialMedicinesSql = " select knowledge_id,\n" +
                "max(case name when '药物名称' then property end) as name,\n" +
                "max(case name when '英文名称' then property end) as english_name,\n" +
                "max(case name when '基本药物分类' then property end) as tag\n" +
                "from released_knowledge_field where\n" +
                "knowledge_id in (select id from released_knowledge where deleted=0 and knowledge_lib_id='" + ESSENTIAL_MEDICINES + "' and updated_time > '" + LocalDate.now().plusDays(-1) + "')\n" +
                " group by knowledge_id";
        List<Words> wordsArray = wordsMapper.executeMultiSelect(essentialMedicinesSql);
        for (Words words : wordsArray) {
            words.setKnowledgeLibId(ESSENTIAL_MEDICINES);
            words.setSourceType(Words.STATUS_KNOWLEDGE);
            words.setDeleted(Words.DELETED_NO);
            words.setStatus(Words.PASS_NO);
            words.setCreatedBy("自动导入");
            words.setUpdatedBy("自动导入");
            words.setCreatedTime(LocalDateTimeUtil.getNowAsString());
            words.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
            List<Words> dbWordsArray = wordsMapper.selectBeansByWherePart("knowledge_lib_id = '" + ESSENTIAL_MEDICINES + "' and knowledge_id = '" + words.getKnowledgeId() + "'");
            if (CollectionUtils.isNotEmpty(dbWordsArray)) {
                words.setId(dbWordsArray.get(0).getId());
            }
            wordsMapper.saveBean(words);
        }
    }

    private void buildCommonPrescription(String COMMON_PRESCRIPTION) {
        String commonPrescriptionSql = " select knowledge_id,\n" +
                "max(case name when '疾病名称' then property end) as name,\n" +
                "max(case name when '常见病处方分类' then property end) as tag\n" +
                "from released_knowledge_field where\n" +
                "knowledge_id in (select id from released_knowledge where deleted=0 and knowledge_lib_id='" + COMMON_PRESCRIPTION + "' and updated_time > '" + LocalDate.now().plusDays(-1) + "')\n" +
                " group by knowledge_id";
        List<Words> wordsArray = wordsMapper.executeMultiSelect(commonPrescriptionSql);
        for (Words words : wordsArray) {
            words.setKnowledgeLibId(COMMON_PRESCRIPTION);
            words.setSourceType(Words.STATUS_KNOWLEDGE);
            words.setDeleted(Words.DELETED_NO);
            words.setStatus(Words.PASS_NO);
            words.setCreatedBy("自动导入");
            words.setUpdatedBy("自动导入");
            words.setCreatedTime(LocalDateTimeUtil.getNowAsString());
            words.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
            List<Words> dbWordsArray = wordsMapper.selectBeansByWherePart("knowledge_lib_id = '" + COMMON_PRESCRIPTION + "' and knowledge_id = '" + words.getKnowledgeId() + "'");
            if (CollectionUtils.isNotEmpty(dbWordsArray)) {
                words.setId(dbWordsArray.get(0).getId());
            }
            wordsMapper.saveBean(words);
        }
    }

    private void buildClinicalOperation(String CLINICAL_OPERATION) {
        String clinicalOperationSql = "select knowledge_id,\n" +
                "max(case name when '名称' then property end) as name,\n" +
                "max(case name when '英文名称' then property end) as english_name,\n" +
                "max(case name when '缩写' then property end) as synonym,\n" +
                "max(case name when '别名' then property end) as synonymBak,\n" +
                "max(case name when '科室分类' then property end) as tag,\n" +
                "max(case name when '临床操作分类' then property end) as tagBak\n" +
                "from released_knowledge_field where\n" +
                "knowledge_id in (select id from released_knowledge where deleted=0 and knowledge_lib_id = '" + CLINICAL_OPERATION + "' and updated_time > '" + LocalDate.now().plusDays(-1) + "')\n" +
                " group by knowledge_id";
        List<Words> wordsArray = wordsMapper.executeMultiSelect(clinicalOperationSql);
        for (Words words : wordsArray) {

            words.setSynonym(Stream.of(words.getSynonym(), words.getSynonymBak())
                    .filter(org.apache.commons.lang.StringUtils::isNotEmpty).collect(Collectors.joining(";")));
            words.setTag(Stream.of(words.getTag(), words.getTagBak())
                    .filter(org.apache.commons.lang.StringUtils::isNotEmpty).collect(Collectors.joining(";")));
            words.setKnowledgeLibId(CLINICAL_OPERATION);
            words.setSourceType(Words.STATUS_KNOWLEDGE);
            words.setDeleted(Words.DELETED_NO);
            words.setStatus(Words.PASS_NO);
            words.setCreatedBy("自动导入");
            words.setUpdatedBy("自动导入");
            words.setCreatedTime(LocalDateTimeUtil.getNowAsString());
            words.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
            List<Words> dbWordsArray = wordsMapper.selectBeansByWherePart("knowledge_lib_id = '" + CLINICAL_OPERATION + "' and knowledge_id = '" + words.getKnowledgeId() + "'");
            if (CollectionUtils.isNotEmpty(dbWordsArray)) {
                words.setId(dbWordsArray.get(0).getId());
            }
            wordsMapper.saveBean(words);
        }
    }

    private void buildChineseMedicine(String CHINESE_MEDICINE) {
        String chineseMedicineSql = " select knowledge_id,\n" +
                "max(case name when '中药名称' then property end) as name,\n" +
                "max(case name when '中药分类' then property end) as tag\n" +
                "from released_knowledge_field where\n" +
                "knowledge_id in (select id from released_knowledge where deleted=0 and knowledge_lib_id='" + CHINESE_MEDICINE + "' and updated_time > '" + LocalDate.now().plusDays(-1) + "' )\n" +
                " group by knowledge_id";
        List<Words> wordsArray = wordsMapper.executeMultiSelect(chineseMedicineSql);
        for (Words words : wordsArray) {
            words.setKnowledgeLibId(CHINESE_MEDICINE);
            words.setSourceType(Words.STATUS_KNOWLEDGE);
            words.setDeleted(Words.DELETED_NO);
            words.setStatus(Words.PASS_NO);
            words.setCreatedBy("自动导入");
            words.setUpdatedBy("自动导入");
            words.setCreatedTime(LocalDateTimeUtil.getNowAsString());
            words.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
            List<Words> dbWordsArray = wordsMapper.selectBeansByWherePart("knowledge_lib_id = '" + CHINESE_MEDICINE + "' and knowledge_id = '" + words.getKnowledgeId() + "'");
            if (CollectionUtils.isNotEmpty(dbWordsArray)) {
                words.setId(dbWordsArray.get(0).getId());
            }
            wordsMapper.saveBean(words);
        }
    }

    private void buildPrescriptions(String PRESCRIPTIONS) {
        String prescriptionsSql = "  select knowledge_id,\n" +
                "max(case name when '方剂名称' then property end) as name,\n" +
                "max(case name when '异名' then property end) as synonym,\n" +
                "max(case name when '方剂分类' then property end) as tag\n" +
                "from released_knowledge_field where\n" +
                "knowledge_id in (select id from released_knowledge where deleted=0 and knowledge_lib_id='" + PRESCRIPTIONS + "' and updated_time > '" + LocalDate.now().plusDays(-1) + "' )\n" +
                "group by knowledge_id";
        List<Words> wordsArray = wordsMapper.executeMultiSelect(prescriptionsSql);
        for (Words words : wordsArray) {
            words.setKnowledgeLibId(PRESCRIPTIONS);
            words.setSourceType(Words.STATUS_KNOWLEDGE);
            words.setDeleted(Words.DELETED_NO);
            words.setStatus(Words.PASS_NO);
            words.setCreatedBy("自动导入");
            words.setUpdatedBy("自动导入");
            words.setCreatedTime(LocalDateTimeUtil.getNowAsString());
            words.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
            List<Words> dbWordsArray = wordsMapper.selectBeansByWherePart("knowledge_lib_id = '" + PRESCRIPTIONS + "' and knowledge_id = '" + words.getKnowledgeId() + "'");
            if (CollectionUtils.isNotEmpty(dbWordsArray)) {
                words.setId(dbWordsArray.get(0).getId());
            }
            wordsMapper.saveBean(words);
        }
    }

    private void buildDisease(String DISEASE) {
        String diseaseSql = " select knowledge_id,\n" +
                "max(case name when '疾病名' then property end) as name,\n" +
                "max(case name when '英文名称' then property end) as english_name,\n" +
                "max(case name when '英文别名' then property end) as english_name_bak,\n" +
                "max(case name when '中文别名' then property end) as synonym,\n" +
                "max(case name when '科室分类' then property end) as tag,\n" +
                "max(case name when '人卫icd_10' then property end) as tagBak\n" +
                "from released_knowledge_field where\n" +
                "knowledge_id in (select id from released_knowledge where deleted=0 and knowledge_lib_id='" + DISEASE + "' and updated_time > '" + LocalDate.now().plusDays(-1) + "' ) \n" +
                "group by knowledge_id";
        List<Words> wordsArray = wordsMapper.executeMultiSelect(diseaseSql);
        for (Words words : wordsArray) {
            String englishName = JSON.parseArray(words.getEnglishName()).stream().map(Object::toString).collect(Collectors.joining(";"));
            String englishNameBak = JSON.parseArray(words.getEnglishNameBak()).stream().map(Object::toString).collect(Collectors.joining(";"));
            words.setEnglishName(Stream.of(englishName, englishNameBak)
                    .filter(org.apache.commons.lang.StringUtils::isNotEmpty).collect(Collectors.joining(";")));
            words.setTag(Stream.of(words.getTag(), words.getTagBak())
                    .filter(org.apache.commons.lang.StringUtils::isNotEmpty).filter(tagStr -> !JSON.parseArray(tagStr).isEmpty()).collect(Collectors.joining(";")));
            String synonym = JSON.parseArray(words.getSynonym()).stream().map(Object::toString).collect(Collectors.joining(";"));
            words.setSynonym(synonym);
            words.setKnowledgeLibId(DISEASE);
            words.setSourceType(Words.STATUS_KNOWLEDGE);
            words.setDeleted(Words.DELETED_NO);
            words.setStatus(Words.PASS_NO);
            words.setCreatedBy("自动导入");
            words.setUpdatedBy("自动导入");
            words.setCreatedTime(LocalDateTimeUtil.getNowAsString());
            words.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
            List<Words> dbWordsArray = wordsMapper.selectBeansByWherePart("knowledge_lib_id = '" + DISEASE + "' and knowledge_id = '" + words.getKnowledgeId() + "'");
            if (CollectionUtils.isNotEmpty(dbWordsArray)) {
                words.setId(dbWordsArray.get(0).getId());
            }
            wordsMapper.saveBean(words);
        }
    }

    private void buildNursingProcedures(String NURSING_PROCEDURES) {
        String nursingProceduresSql = "select knowledge_id,\n" +
                "max(case name when '操作名称' then property end) as name,\n" +
                "max(case name when '英文名称' then property end) as english_name,\n" +
                "max(case name when '别名' then property end) as synonym,\n" +
                "max(case name when '缩写' then property end) as synonymBak,\n" +
                "max(case name when '科室分类' then property end) as tag,\n" +
                "max(case name when '护理操作分类' then property end) as tagBak\n" +
                "from released_knowledge_field where\n" +
                "knowledge_id in (select id from released_knowledge where deleted=0 and knowledge_lib_id='" + NURSING_PROCEDURES + "' and updated_time > '" + LocalDate.now().plusDays(-1) + "' ) \n" +
                "group by knowledge_id";
        List<Words> wordsArray = wordsMapper.executeMultiSelect(nursingProceduresSql);
        for (Words words : wordsArray) {
            words.setSynonym(Stream.of(words.getSynonym(), words.getSynonymBak())
                    .filter(org.apache.commons.lang.StringUtils::isNotEmpty).collect(Collectors.joining(";")));
            words.setTag(Stream.of(words.getTag(), words.getTagBak())
                    .filter(org.apache.commons.lang.StringUtils::isNotEmpty).filter(tagStr -> !JSON.parseArray(tagStr).isEmpty()).collect(Collectors.joining(";")));
            words.setKnowledgeLibId(NURSING_PROCEDURES);
            words.setSourceType(Words.STATUS_KNOWLEDGE);
            words.setDeleted(Words.DELETED_NO);
            words.setStatus(Words.PASS_NO);
            words.setCreatedBy("自动导入");
            words.setUpdatedBy("自动导入");
            words.setCreatedTime(LocalDateTimeUtil.getNowAsString());
            words.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
            List<Words> dbWordsArray = wordsMapper.selectBeansByWherePart("knowledge_lib_id = '" + NURSING_PROCEDURES + "' and knowledge_id = '" + words.getKnowledgeId() + "'");
            if (CollectionUtils.isNotEmpty(dbWordsArray)) {
                words.setId(dbWordsArray.get(0).getId());
            }
            wordsMapper.saveBean(words);
        }
    }

    private void buildOperation(String OPERATION) {
        String operationSql = " select knowledge_id,\n" +
                "max(case name when '中文名称' then property end) as name,\n" +
                "max(case name when '英文名称' then property end) as english_name,\n" +
                "max(case name when '别名' then property end) as synonym,\n" +
                "max(case name when '英文缩写' then property end) as synonymBak,\n" +
                "max(case name when '手术操作分类' then property end) as tag,\n" +
                "max(case name when '科室分类' then property end) as tagBak\n" +
                "from released_knowledge_field where knowledge_id in (select id from released_knowledge where deleted=0 and knowledge_lib_id='" + OPERATION + "' and updated_time > '" + LocalDate.now().plusDays(-1) + "') \n" +
                " group by knowledge_id";
        List<Words> wordsArray = wordsMapper.executeMultiSelect(operationSql);
        for (Words words : wordsArray) {
            String englishName = JSON.parseArray(words.getEnglishName()).stream().map(Object::toString).collect(Collectors.joining(";"));
            words.setEnglishName(englishName);
            String synonym = JSON.parseArray(words.getSynonym()).stream().map(Object::toString).collect(Collectors.joining(";"));
            String synonymBak = JSON.parseArray(words.getSynonymBak()).stream().map(Object::toString).collect(Collectors.joining(";"));
            words.setSynonym(Stream.of(synonym, synonymBak)
                    .filter(org.apache.commons.lang.StringUtils::isNotEmpty).collect(Collectors.joining(";")));
            words.setTag(Stream.of(words.getTag(), words.getTagBak())
                    .filter(org.apache.commons.lang.StringUtils::isNotEmpty).filter(tagStr -> !JSON.parseArray(tagStr).isEmpty()).collect(Collectors.joining(";")));
            words.setKnowledgeLibId(OPERATION);
            words.setSourceType(Words.STATUS_KNOWLEDGE);
            words.setDeleted(Words.DELETED_NO);
            words.setStatus(Words.PASS_NO);
            words.setCreatedBy("自动导入");
            words.setUpdatedBy("自动导入");
            words.setCreatedTime(LocalDateTimeUtil.getNowAsString());
            words.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
            List<Words> dbWordsArray = wordsMapper.selectBeansByWherePart("knowledge_lib_id = '" + OPERATION + "' and knowledge_id = '" + words.getKnowledgeId() + "'");
            if (CollectionUtils.isNotEmpty(dbWordsArray)) {
                words.setId(dbWordsArray.get(0).getId());
            }
            wordsMapper.saveBean(words);
        }
    }

    private void buildSignsSymptoms(String SIGNS_SYMPTOMS) {
        String signsSymptomsSql = "select knowledge_id,\n" +
                "max(case name when '名称' then property end) as name,\n" +
                "max(case name when '英文名称' then property end) as english_name,\n" +
                "max(case name when '别名' then property end) as synonym,\n" +
                "max(case name when '英文缩写' then property end) as synonymBak,\n" +
                "max(case name when '症状体征分类' then property end) as tag\n" +
                "from released_knowledge_field  where\n" +
                "knowledge_id in (select id from released_knowledge where deleted=0 and knowledge_lib_id='" + SIGNS_SYMPTOMS + "' and updated_time > '" + LocalDate.now().plusDays(-1) + "' ) \n" +
                "group by knowledge_id";
        List<Words> wordsArray = wordsMapper.executeMultiSelect(signsSymptomsSql);
        for (Words words : wordsArray) {
            String englishName = JSON.parseArray(words.getEnglishName()).stream().map(Object::toString).collect(Collectors.joining(";"));
            words.setEnglishName(englishName);
            String synonym = JSON.parseArray(words.getSynonym()).stream().map(Object::toString).collect(Collectors.joining(";"));
            String synonymBak = JSON.parseArray(words.getSynonymBak()).stream().map(Object::toString).collect(Collectors.joining(";"));
            words.setSynonym(Stream.of(synonym, synonymBak)
                    .filter(org.apache.commons.lang.StringUtils::isNotEmpty).collect(Collectors.joining(";")));
            words.setKnowledgeLibId(SIGNS_SYMPTOMS);
            words.setSourceType(Words.STATUS_KNOWLEDGE);
            words.setDeleted(Words.DELETED_NO);
            words.setStatus(Words.PASS_NO);
            words.setCreatedBy("自动导入");
            words.setUpdatedBy("自动导入");
            words.setCreatedTime(LocalDateTimeUtil.getNowAsString());
            words.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
            List<Words> dbWordsArray = wordsMapper.selectBeansByWherePart("knowledge_lib_id = '" + SIGNS_SYMPTOMS + "' and knowledge_id = '" + words.getKnowledgeId() + "'");
            if (CollectionUtils.isNotEmpty(dbWordsArray)) {
                words.setId(dbWordsArray.get(0).getId());
            }
            wordsMapper.saveBean(words);
        }
    }

    private void buildTcmDisease(String TCM_DISEASE) {
        String tcmDiseaseSql = "select knowledge_id,\n" +
                "max(case name when '名称' then property end) as name,\n" +
                "max(case name when '疾病分类' then property end) as tag\n" +
                "from released_knowledge_field \n" +
                "where knowledge_id in (select id from released_knowledge where deleted=0 and knowledge_lib_id='" + TCM_DISEASE + "' and updated_time > '" + LocalDate.now().plusDays(-1) + "' )group by\n" +
                "knowledge_id";
        List<Words> wordsArray = wordsMapper.executeMultiSelect(tcmDiseaseSql);
        for (Words words : wordsArray) {
            words.setKnowledgeLibId(TCM_DISEASE);
            words.setSourceType(Words.STATUS_KNOWLEDGE);
            words.setDeleted(Words.DELETED_NO);
            words.setStatus(Words.PASS_NO);
            words.setCreatedBy("自动导入");
            words.setUpdatedBy("自动导入");
            words.setCreatedTime(LocalDateTimeUtil.getNowAsString());
            words.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
            List<Words> dbWordsArray = wordsMapper.selectBeansByWherePart("knowledge_lib_id = '" + TCM_DISEASE + "' and knowledge_id = '" + words.getKnowledgeId() + "'");
            if (CollectionUtils.isNotEmpty(dbWordsArray)) {
                words.setId(dbWordsArray.get(0).getId());
            }
            wordsMapper.saveBean(words);
        }
    }

    private void buildChannels(String CHANNELS) {
        String channelsSql = "select knowledge_id,\n" +
                "max(case name when '经络名称' then property end) as name\n" +
                "from released_knowledge_field \n" +
                "where knowledge_id in (select id from released_knowledge where deleted=0 and knowledge_lib_id='" + CHANNELS + "' and updated_time > '" + LocalDate.now().plusDays(-1) + "' )group by\n" +
                "knowledge_id";
        List<Words> wordsArray = wordsMapper.executeMultiSelect(channelsSql);
        for (Words words : wordsArray) {
            words.setKnowledgeLibId(CHANNELS);
            words.setSourceType(Words.STATUS_KNOWLEDGE);
            words.setDeleted(Words.DELETED_NO);
            words.setStatus(Words.PASS_NO);
            words.setCreatedBy("自动导入");
            words.setUpdatedBy("自动导入");
            words.setCreatedTime(LocalDateTimeUtil.getNowAsString());
            words.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
            List<Words> dbWordsArray = wordsMapper.selectBeansByWherePart("knowledge_lib_id = '" + CHANNELS + "' and knowledge_id = '" + words.getKnowledgeId() + "'");
            if (CollectionUtils.isNotEmpty(dbWordsArray)) {
                words.setId(dbWordsArray.get(0).getId());
            }
            wordsMapper.saveBean(words);
        }
    }

    private void buildMedicine(String MEDICINE) {
        String medicineSql = "select knowledge_id,\n" +
                "max(case name when '中成药名称' then property end) as name,\n" +
                "max(case name when '中成药分类' then property end) as tag\n" +
                "from released_knowledge_field \n" +
                "where knowledge_id in (select id from released_knowledge where deleted=0 and knowledge_lib_id='" + MEDICINE + "' and updated_time > '" + LocalDate.now().plusDays(-1) + "' )group by\n" +
                "knowledge_id";
        List<Words> wordsArray = wordsMapper.executeMultiSelect(medicineSql);
        for (Words words : wordsArray) {
            words.setKnowledgeLibId(MEDICINE);
            words.setSourceType(Words.STATUS_KNOWLEDGE);
            words.setDeleted(Words.DELETED_NO);
            words.setStatus(Words.PASS_NO);
            words.setCreatedBy("自动导入");
            words.setUpdatedBy("自动导入");
            words.setCreatedTime(LocalDateTimeUtil.getNowAsString());
            words.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
            List<Words> dbWordsArray = wordsMapper.selectBeansByWherePart("knowledge_lib_id = '" + MEDICINE + "' and knowledge_id = '" + words.getKnowledgeId() + "'");
            if (CollectionUtils.isNotEmpty(dbWordsArray)) {
                words.setId(dbWordsArray.get(0).getId());
            }
            wordsMapper.saveBean(words);
        }
    }

    private void buildPoint(String POINT) {
        String pointSql = "select knowledge_id,\n" +
                "max(case name when '穴位名称' then property end) as name,\n" +
                "max(case name when '别名' then property end) as synonym,\n" +
                "max(case name when '穴位分类' then property end) as tag\n" +
                "from released_knowledge_field \n" +
                "where knowledge_id in (select id from released_knowledge where deleted=0 and knowledge_lib_id='" + POINT + "' and updated_time > '" + LocalDate.now().plusDays(-1) + "'  )group by\n" +
                "knowledge_id";
        List<Words> wordsArray = wordsMapper.executeMultiSelect(pointSql);
        for (Words words : wordsArray) {
            String synonym = JSON.parseArray(words.getSynonym()).stream().map(Object::toString).collect(Collectors.joining(";"));
            words.setSynonym(synonym);
            words.setKnowledgeLibId(POINT);
            words.setSourceType(Words.STATUS_KNOWLEDGE);
            words.setDeleted(Words.DELETED_NO);
            words.setStatus(Words.PASS_NO);
            words.setCreatedBy("自动导入");
            words.setUpdatedBy("自动导入");
            words.setCreatedTime(LocalDateTimeUtil.getNowAsString());
            words.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
            List<Words> dbWordsArray = wordsMapper.selectBeansByWherePart("knowledge_lib_id = '" + POINT + "' and knowledge_id = '" + words.getKnowledgeId() + "'");
            if (CollectionUtils.isNotEmpty(dbWordsArray)) {
                words.setId(dbWordsArray.get(0).getId());
            }
            wordsMapper.saveBean(words);
        }
    }

    private void buildLaboratory(String LABORATORY) {
        String laboratorySql = "select knowledge_id,\n" +
                "max(case name when '名称' then property end) as name,\n" +
                "max(case name when '英文名称' then property end) as english_name,\n" +
                "max(case name when '别名' then property end) as synonym,\n" +
                "max(case name when '化验单分类' then property end) as tag\n" +
                "from released_knowledge_field \n" +
                "where knowledge_id in (select id from released_knowledge where deleted=0 and knowledge_lib_id='" + LABORATORY + "' and updated_time > '" + LocalDate.now().plusDays(-1) + "' )group by\n" +
                "knowledge_id";
        List<Words> wordsArray = wordsMapper.executeMultiSelect(laboratorySql);
        Map<String, List<Words>> collect = wordsArray.stream().collect(Collectors.groupingBy(Words::getKnowledgeId));
        for (Words words : wordsArray) {
            String englishName = JSON.parseArray(words.getEnglishName()).stream().map(Object::toString).collect(Collectors.joining(";"));
            words.setEnglishName(englishName);
            String synonym = JSON.parseArray(words.getSynonym()).stream().map(Object::toString).collect(Collectors.joining(";"));
            words.setEnglishName(synonym);
            words.setKnowledgeLibId(LABORATORY);
            words.setSourceType(Words.STATUS_KNOWLEDGE);
            words.setDeleted(Words.DELETED_NO);
            words.setStatus(Words.PASS_NO);
            words.setCreatedBy("自动导入");
            words.setUpdatedBy("自动导入");
            words.setCreatedTime(LocalDateTimeUtil.getNowAsString());
            words.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
            List<Words> dbWordsArray = wordsMapper.selectBeansByWherePart("knowledge_lib_id = '" + LABORATORY + "' and knowledge_id = '" + words.getKnowledgeId() + "'");
            if (CollectionUtils.isNotEmpty(dbWordsArray)) {
                words.setId(dbWordsArray.get(0).getId());
            }
            wordsMapper.saveBean(words);
        }
    }

    private void inspectionItemBuild(String INSPECTION_ITEM) {
        String inspectionItemSql = "select knowledge_id,\n" +
                "max(case name when '名称' then property end) as name,\n" +
                "max(case name when '英文名称' then property end) as english_name,\n" +
                "max(case name when '别名' then property end) as synonym,\n" +
                "max(case name when '检验分类（2级）' then property end) as tag,\n" +
                "max(case name when '检验分类（5级）' then property end) as db_tag,\n" +
                "max(case name when '科室分类' then property end) as tagBak\n" +
                "from released_knowledge_field where\n" +
                " knowledge_id in (select id from released_knowledge where deleted=0 and knowledge_lib_id='" + INSPECTION_ITEM + "' and updated_time > '" + LocalDate.now().plusDays(-1) + "' ) \n" +
                " group by knowledge_id";
        List<Words> wordsArray = wordsMapper.executeMultiSelect(inspectionItemSql);
        for (Words words : wordsArray) {
            String englishName = JSON.parseArray(words.getEnglishName()).stream().map(Object::toString).collect(Collectors.joining(";"));
            words.setEnglishName(englishName);
            String synonym = JSON.parseArray(words.getSynonym()).stream().map(Object::toString).collect(Collectors.joining(";"));
            words.setSynonym(synonym);
            words.setTag(Stream.of(words.getTag(), words.getTagBak(), words.getDbTag())
                    .filter(org.apache.commons.lang.StringUtils::isNotEmpty).filter(tagStr -> !JSON.parseArray(tagStr).isEmpty()).collect(Collectors.joining(";")));
            words.setKnowledgeLibId(INSPECTION_ITEM);
            words.setSourceType(Words.STATUS_KNOWLEDGE);
            words.setDeleted(Words.DELETED_NO);
            words.setStatus(Words.PASS_NO);
            words.setCreatedBy("自动导入");
            words.setUpdatedBy("自动导入");
            words.setCreatedTime(LocalDateTimeUtil.getNowAsString());
            words.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
            List<Words> dbWordsArray = wordsMapper.selectBeansByWherePart("knowledge_lib_id = '" + INSPECTION_ITEM + "' and knowledge_id = '" + words.getKnowledgeId() + "'");
            if (CollectionUtils.isNotEmpty(dbWordsArray)) {
                words.setId(dbWordsArray.get(0).getId());
            }
            wordsMapper.saveBean(words);
        }
    }

    private void buildDiseaseAli(String DISEASE_ALI) {
        String diseaseAliSql = "select knowledge_id,\n" +
                "max(case name when '名称' then property end) as name,\n" +
                "max(case name when '别名' then property end) as synonym\n" +
                "from released_knowledge_field where\n" +
                "knowledge_id in (select id from released_knowledge where deleted=0 and knowledge_lib_id='" + DISEASE_ALI + "' and updated_time > '" + LocalDate.now().plusDays(-1) + "' ) \n" +
                "group by  knowledge_id";
        List<Words> wordsArray = wordsMapper.executeMultiSelect(diseaseAliSql);
        for (Words words : wordsArray) {
            words.setKnowledgeLibId(DISEASE_ALI);
            words.setSourceType(Words.STATUS_KNOWLEDGE);
            words.setDeleted(Words.DELETED_NO);
            words.setStatus(Words.PASS_NO);
            words.setCreatedBy("自动导入");
            words.setUpdatedBy("自动导入");
            words.setCreatedTime(LocalDateTimeUtil.getNowAsString());
            words.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
            List<Words> dbWordsArray = wordsMapper.selectBeansByWherePart("knowledge_lib_id = '" + DISEASE_ALI + "' and knowledge_id = '" + words.getKnowledgeId() + "'");
            if (CollectionUtils.isNotEmpty(dbWordsArray)) {
                words.setId(dbWordsArray.get(0).getId());
            }
            wordsMapper.saveBean(words);
        }
    }

    @NotNull
    private void buildCheck(String CHECK) {
        String checkSql = "select knowledge_id,\n" +
                "max(case name when '名称' then property end) as name,\n" +
                "max(case name when '英文名称' then property end) as english_name,\n" +
                "max(case name when '英文缩写' then property end) as english_name_bak,\n" +
                "max(case name when '别称' then property end) as synonym,\n" +
                "max(case name when '专科分类' then property end) as tag,\n" +
                "max(case name when '检查分类' then property end) as tagBak\n" +
                "from released_knowledge_field \n" +
                "where knowledge_id in (select id from released_knowledge where deleted=0 and knowledge_lib_id='" + CHECK + "' and updated_time > '" + LocalDate.now().plusDays(-1) + "' ) \n" +
                "group by knowledge_id";
        List<Words> wordsArray = wordsMapper.executeMultiSelect(checkSql);
        for (Words words : wordsArray) {
            String englishName = JSON.parseArray(words.getEnglishName()).stream().map(Object::toString).collect(Collectors.joining(";"));
            String englishNameBak = JSON.parseArray(words.getEnglishNameBak()).stream().map(Object::toString).collect(Collectors.joining(";"));
            words.setEnglishName(Stream.of(englishName, englishNameBak).filter(org.apache.commons.lang.StringUtils::isNotEmpty).collect(Collectors.joining(";")));
            String synonym = JSON.parseArray(words.getSynonym()).stream().map(Object::toString).collect(Collectors.joining(";"));
            words.setSynonym(synonym);
            words.setTag(Stream.of(words.getTag(), words.getTagBak())
                    .filter(org.apache.commons.lang.StringUtils::isNotEmpty).filter(tagStr -> !JSON.parseArray(tagStr).isEmpty()).collect(Collectors.joining(";")));
            words.setKnowledgeLibId(CHECK);
            words.setSourceType(Words.STATUS_KNOWLEDGE);
            words.setDeleted(Words.DELETED_NO);
            words.setStatus(Words.PASS_NO);
            words.setCreatedBy("自动导入");
            words.setUpdatedBy("自动导入");
            words.setCreatedTime(LocalDateTimeUtil.getNowAsString());
            words.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
            List<Words> dbWordsArray = wordsMapper.selectBeansByWherePart("knowledge_lib_id = '" + CHECK + "' and knowledge_id = '" + words.getKnowledgeId() + "'");
            if (CollectionUtils.isNotEmpty(dbWordsArray)) {
                words.setId(dbWordsArray.get(0).getId());
                words.setUpdatedTime(LocalDateTimeUtil.getNowAsString());

            }
            wordsMapper.saveBean(words);
        }
    }

    private void buildDrugInformation(String DRUG_INFORMATION) {
        // 查询昨天修改的药物信息的知识名称;
        List<ReleasedKnowledge> knowledgeList = releasedKnowledgeService.lambdaQuery()
                .select(Knowledge::getName)
                .eq(Knowledge::getKnowledgeLibId, DRUG_INFORMATION)
                .eq(Knowledge::getDeleted, Knowledge.DELETED_NO)
                .gt(Knowledge::getUpdatedTime, LocalDate.now().plusDays(-1))
                .list();
        if (CollectionUtils.isEmpty(knowledgeList)) {
            return;
        }
        // 查询同名药物信息ID;
        knowledgeList = releasedKnowledgeService.lambdaQuery()
                .select(Knowledge::getId)
                .eq(Knowledge::getKnowledgeLibId, DRUG_INFORMATION)
                .eq(Knowledge::getDeleted, Knowledge.DELETED_NO)
                .in(Knowledge::getName, knowledgeList.stream().map(Knowledge::getName).collect(Collectors.toList()))
                .list();
        String drugInformationSql = "select knowledge_id, max(case name when '通用名称' then property end) as name,\n" +
                "max(case name when '英文名称' then property end) as english_name,\n" +
                "max(case name when '商品名/商标名' then property end) as synonym,\n" +
                "max(case name when '其他名称' then property end) as synonymBak,\n" +
                "max(case name when '原研药' then property end) as tag\n" +
                "from released_knowledge_field where\n" +
                "knowledge_id in " + knowledgeList.stream().map(Knowledge::getId).collect(Collectors.joining("','", "('", "')")) +
                " and name in ('通用名称', '英文名称', '商品名/商标名', '其他名称', '原研药')\n" +
                "group by knowledge_id";
        List<Words> wordsArray = wordsMapper.executeMultiSelect(drugInformationSql);

        Map<String, List<Words>> collect = wordsArray.stream().collect(Collectors.groupingBy(Words::getName));
        for (String name : collect.keySet()) {
            Words words = new Words();
            words.setName(name);
            List<Words> wordsList = collect.get(name);
            words.setEnglishName(wordsList.stream().map(Words::getEnglishName).distinct().filter(org.apache.commons.lang.StringUtils::isNotBlank).collect(Collectors.joining(";")));
            words.setSynonym(Stream.concat(wordsList.stream().map(Words::getSynonym), wordsList.stream().map(Words::getSynonymBak))
                    .distinct().filter(org.apache.commons.lang.StringUtils::isNotBlank).collect(Collectors.joining(";")));
            words.setTag(wordsList.stream().map(Words::getTag)
                    .filter(org.apache.commons.lang.StringUtils::isNotBlank)
                    .flatMap(tag -> JSON.parseArray(tag).stream())
                    .map(JSON::toJSONString).distinct().filter(innerTagArrayStr -> !JSON.parseArray(innerTagArrayStr).isEmpty())
                    .collect(Collectors.joining(",", "[", "]")));
            words.setKnowledgeId(wordsList.stream().map(Words::getKnowledgeId).distinct().collect(Collectors.joining(";")));
            words.setKnowledgeLibId(DRUG_INFORMATION);
            words.setSourceType(Words.STATUS_KNOWLEDGE);
            words.setDeleted(Words.DELETED_NO);
            words.setStatus(Words.PASS_NO);
            words.setCreatedBy("自动导入");
            words.setUpdatedBy("自动导入");
            words.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
            List<Words> dbWordsArray = wordsMapper.selectBeansByWherePart("knowledge_lib_id = '" + DRUG_INFORMATION + "' and name = '" + name + "'");
            if (CollectionUtils.isNotEmpty(dbWordsArray)) {
                words.setId(dbWordsArray.get(0).getId());
                words.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
            }
            wordsMapper.saveBean(words);
        }
    }

    private void termStandard2words() {
        String termSql = "select id as knowledge_id,name,synonym,english_name,english_abbr english_name_bak,english_abbr_synonym english_name_bak1,updated_time from term_standard where deleted = 0";
        List<Words> wordsArray = wordsMapper.executeMultiSelect(termSql);
        for (Words words : wordsArray) {
            if (StringUtils.isNotBlank(words.getSynonym())) {
                String value = JSON.parseArray(words.getSynonym()).stream().map(obj -> ((JSONObject) obj).getString("value")).collect(Collectors.joining());
                words.setSynonym(value);
            }
            words.setEnglishName(Stream.of(words.getEnglishName(), words.getEnglishNameBak(), words.getEnglishNameBak1())
                    .filter(org.apache.commons.lang.StringUtils::isNotEmpty).collect(Collectors.joining(";")));
            words.setCreatedBy("自动导入");
            words.setCreatedTime(LocalDateTimeUtil.getNowAsString());
            words.setUpdatedBy("自动导入");
            words.setUpdatedTime(LocalDateTimeUtil.getNowAsString());
            words.setSourceType(Words.STATUS_TERM);
            // 查询词语库是否存在该术语;
            List<Words> dbBeans = wordsMapper.selectBeansByWherePart(" knowledge_id = '" + words.getKnowledgeId() + "'");
            if (CollectionUtils.isNotEmpty(dbBeans)) {
                words.setId(dbBeans.get(0).getId());
                words.setUpdatedTime(dbBeans.get(0).getUpdatedTime());
            }
            wordsMapper.saveBean(words);
        }
    }
}
